package zhuzx.solution;

import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.SimpleMongoDbFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.zhuzx.util.ExcelUtil;
import org.zhuzx.util.FileOperationUtil;
import org.zhuzx.util.GeneralUtil;
import org.zhuzx.util.HardwareVariables;
import org.zhuzx.util.MicroUtil;

import com.alibaba.druid.pool.DruidDataSource;
import com.mongodb.MongoClientOptions;
import com.mongodb.MongoClientURI;

/**
 * 导出数据用的辅助工具
 * @author zhuzx 2019年06月17日
 */
public class ExportHelper {
	
	static JdbcTemplate createJdbcTemplate() {
		String confFileName = "zhuzx/shared/db.properties";
		Map<String,String> conf = GeneralUtil.loadPropsFileFromClasspath(confFileName);
		return createJdbcTemplate(conf.get("jdbcUrl"), conf.get("user"),
				conf.get("password"), conf.get("driverClass"));
	}
	
	static JdbcTemplate createJdbcTemplate(
			String url, String username, String password, String driveName){
		DruidDataSource dataSource = new DruidDataSource();
		dataSource.setDriverClassName(driveName);
		dataSource.setUrl(url);
		dataSource.setUsername(username);
		dataSource.setPassword(password);
		//如果用Oracle则为true, mysql和分库分表较多的建议flase。
		dataSource.setPoolPreparedStatements(false);
		dataSource.setInitialSize(1);
		dataSource.setName(UUID.randomUUID().toString());
		dataSource.setMinIdle(HardwareVariables.CPU_THREAD_NUM);
		dataSource.setMaxActive(HardwareVariables.CPU_THREAD_NUM * 2);
		dataSource.setMaxWait(30000);
		dataSource.setRemoveAbandoned(true);
		dataSource.setRemoveAbandonedTimeout(18000);
		dataSource.setLogAbandoned(true);
		dataSource.setTimeBetweenEvictionRunsMillis(60000);
		dataSource.setMinEvictableIdleTimeMillis(300000);
		dataSource.setValidationQuery("SELECT 'x'");
		dataSource.setTestWhileIdle(true);
		dataSource.setTestOnBorrow(false);
		dataSource.setTestOnReturn(false);
		dataSource.setPoolPreparedStatements(false);
		dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
		JdbcTemplate jdbcTemplate = new JdbcTemplate();
		jdbcTemplate.setDataSource(dataSource);
		System.out.println("JdbcTemplate初始化成功");
		return jdbcTemplate;
	}
	
	static MongoTemplate createMongoTemplate(String uri) {
		try {
			MongoClientOptions.Builder mongoClientOptions = MongoClientOptions.builder()
					.socketTimeout(300000)
					.connectTimeout(100000);
			MongoTemplate mongo = new MongoTemplate(new SimpleMongoDbFactory(
					new MongoClientURI(uri,mongoClientOptions)));
			System.out.println("MongoTemplate初始化成功");
			return mongo;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public static String createCsvString(List<Map<String,String>> result) {
		StringBuilder sb = new StringBuilder();
		byte[] bom = {(byte) 0xEF, (byte) 0xBB, (byte) 0xBF};
		sb.append(new String(bom, StandardCharsets.UTF_8));
		for (String key : result.get(0).keySet()) {
			sb.append(key).append(",");
		}
		sb.append("\n");
		for (Map<String,String> map : result) {
			for (Map.Entry<String,String> entry : map.entrySet()) {
				String value = entry.getValue();
				if (value == null) {
					value = "";
				}
				if (value.length() > 0) {
					value = value.replaceAll("[\n,]", " ");
				}
				sb.append(value).append(",");
			}
			sb.append("\n");
		}
		return sb.toString();
	}
	
	public static void createExcelByChineseHeader(String sql, String fileName) {
//		if (!sql.trim().replaceAll("\\s+", " ").toLowerCase().startsWith("select * from ")) {
//			throw new IllegalArgumentException("仅支持select *语句，当前sql会导致列名错乱：" + sql);
//		}
		byte[] bytes = ExcelUtil.createExcel(true, null, false, sql);
		if (fileName == null) {
			fileName = MicroUtil.extractFirstTableName(sql);
		}
		FileOperationUtil.writeToFile(bytes, fileName + ".xlsx", true);
	}
	
}